package cn.sylinx.horm.dialect.sql;

import cn.sylinx.horm.core.common.TypedParameter;
import cn.sylinx.horm.dialect.fs.FS;
import cn.sylinx.horm.exception.HORMException;
import cn.sylinx.horm.model.base.BaseModel;
import cn.sylinx.horm.model.base.Model;
import cn.sylinx.horm.model.cache.ModelCacheUtil;
import cn.sylinx.horm.model.cache.ModelFabric;
import cn.sylinx.horm.type.handler.TypeHandler;
import cn.sylinx.horm.util.Pair;
import cn.sylinx.horm.util.StrKit;
import cn.sylinx.horm.util.Tuple;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

public final class DefaultFlusentSqlBuildHelper {

    public static Tuple buildSelectSQL(FS<?> fluentSql) {

        StringBuilder sql = new StringBuilder();
        if (StrKit.isNotBlank(fluentSql.getHint())) {
            sql.append(fluentSql.getHint()).append(" ");
        }

        sql.append("SELECT");
        if (fluentSql.isDistinct()) {
            sql.append(" DISTINCT");
        }

        String singleField = null;
        Class<?> singleFieldClass = null;

        if (fluentSql.isSingleField()) {

            // 如果只取1列
            if (StrKit.isBlank(fluentSql.getSelectColumns())
                    && StrKit.isBlank(fluentSql.getSelectExpressionColumns())) {
                throw new HORMException("need one column at least");
            }

            singleFieldClass = Object.class;

            if (StrKit.isNotBlank(fluentSql.getSelectColumns())) {
                // 首先使用selectColumns内容, 其次使用 selectExpressionColumns，native sql表达式
                singleField = fluentSql.getSelectColumns().split(",")[0].trim();

                String bingoAttr = null;
                Map<String, String> attrs = fluentSql.getAttrs();
                for (Map.Entry<String, String> entry : attrs.entrySet()) {
                    if (singleField.equals(entry.getKey()) || singleField.equals(entry.getValue())) {
                        bingoAttr = entry.getKey();
                    }
                }

                if (bingoAttr == null) {
                    throw new HORMException("no field specified");
                }

                Map<String, Field> fieldMap = ModelCacheUtil.getModelFabric(fluentSql.getModelClass()).getFieldMap();
                singleFieldClass = fieldMap.get(bingoAttr).getType();

            }

        }

        boolean hasSelected = false;
        if (StrKit.isNotBlank(fluentSql.getSelectColumns())) {
            sql.append(' ').append(fluentSql.getSelectColumns()).append(' ');
            hasSelected = true;
        } else if (fluentSql.getExcludedColumns() != null && !fluentSql.getExcludedColumns().isEmpty()) {
            sql.append(' ').append(buildExcludedColumns(fluentSql.getModelClass(), fluentSql.getExcludedColumns(), true,
                    fluentSql.getPreEscape(), fluentSql.getPostEscape())).append(' ');
            hasSelected = true;
        }

        if (StrKit.isNotBlank(fluentSql.getSelectExpressionColumns())) {
            // 如果表达式不为空
            sql.append(hasSelected ? "," : "").append(" ").append(fluentSql.getSelectExpressionColumns()).append(" ");

        } else if (!hasSelected) {
            sql.append(" * ");
        }

        sql.append("FROM ").append(fluentSql.getTableName());
        if (fluentSql.getConditionSQL().length() > 0) {
            sql.append(" WHERE ").append(fluentSql.getConditionSQL().substring(FS.AND_STR.length()));
        }

        String groupBy = fluentSql.getGroupBySQL().toString();
        if (StrKit.isNotBlank(groupBy)) {
            sql.append(" GROUP BY").append(groupBy);
        }

        String orderBy = fluentSql.getOrderBySQL().toString();
        if (StrKit.isNotBlank(orderBy)) {
            sql.append(" ORDER BY").append(orderBy);
        }

        String limitSql = fluentSql.getLimitSQL().toString();
        if (StrKit.isNotBlank(limitSql)) {
            sql.append(limitSql);
        }

        int len = fluentSql.getParamValues() == null || fluentSql.getParamValues().isEmpty() ? 0
                : fluentSql.getParamValues().size();
        Object[] params = null;

        if (len > 0) {
            params = new Object[len];
            fluentSql.getParamValues().toArray(params);
        }

        return Tuple.apply(sql.toString(), params,
                (singleFieldClass == null ? fluentSql.getModelClass() : singleFieldClass));
    }

    public static Pair buildUpdateSQL(FS<?> fluentSql) {

        List<Object> paramsUpdate = new ArrayList<Object>();

        StringBuilder sql = new StringBuilder();
        if (StrKit.isNotBlank(fluentSql.getHint())) {
            sql.append(fluentSql.getHint()).append(" ");
        }

        sql.append("UPDATE ").append(fluentSql.getTableName()).append(" SET ");

        StringBuilder setSQL = new StringBuilder();

        boolean gmtUpdated = false;

        if (null != fluentSql.getUpdateColumnExpression() && !fluentSql.getUpdateColumnExpression().isEmpty()) {
            // native sql expression
            fluentSql.getUpdateColumnExpression()
                    .forEach((key, value) -> setSQL.append(key).append(" = ").append(value).append(", "));

            if (BaseModel.class.isAssignableFrom(fluentSql.getModelClass())) {
                // 设置更新时间
                String gmtModify = fluentSql.mapColumn("gmtModify");
                if (!fluentSql.getUpdateColumnExpression().containsKey(gmtModify)) {
                    setSQL.append(gmtModify).append(" = ?, ");
                    paramsUpdate.add(new Date());
                    gmtUpdated = true;
                }
            }
        }

        if (null != fluentSql.getUpdateColumns() && !fluentSql.getUpdateColumns().isEmpty()) {

            fluentSql.getUpdateColumns().forEach((key, value) -> {
                if (value == null) {
                    // 值为空情况
                    setSQL.append(key).append(" = NULL, ");
                } else {
                    setSQL.append(key).append(" = ?, ");
                    paramsUpdate.add(value);
                }
            });

            if (!gmtUpdated && BaseModel.class.isAssignableFrom(fluentSql.getModelClass())) {
                // 设置更新时间
                String gmtModify = fluentSql.mapColumn("gmtModify");
                if (!fluentSql.getUpdateColumns().containsKey(gmtModify)) {
                    setSQL.append(gmtModify).append(" = ?, ");
                    paramsUpdate.add(new Date());
                }
            }

        } else {
            if (null != fluentSql.getModel()) {

                ModelFabric mf = ModelCacheUtil.getModelFabric(fluentSql.getModelClass());
                // 获取所有字段
                List<Field> fields = mf.getFields();
                Map<String, String> attrs = mf.getAttrMapping();
                Map<String, TypeHandler<?>> typeHandlerMap = mf.getTypeHandlerMap();

                // 获取可设置空字段值
                Set<String> nullableSets = new HashSet<String>();
                if (fluentSql.getModel() instanceof Model) {
                    nullableSets = ((Model) fluentSql.getModel()).getNullableFields();
                }

                if (fluentSql.getModel() instanceof BaseModel) {
                    ((BaseModel) fluentSql.getModel()).setGmtModify(new Date());
                }

                try {
                    for (Field field : fields) {

                        field.setAccessible(true);
                        String fieldName = field.getName();
                        Object v = field.get(fluentSql.getModel());
                        String f = attrs.get(fieldName);
                        if (f != null) {// 找到字段
                            boolean nullable = nullableSets.contains(fieldName);// 可为空
                            if (v != null) {
                                setSQL.append(f).append(" = ?, ");
                                paramsUpdate.add(convertValue(typeHandlerMap.get(fieldName), v));
                            } else if (nullable) {
                                // 可为空，则设置null值
                                setSQL.append(f).append(" = NULL, ");
                            }
                        }
                    }
                } catch (IllegalArgumentException | IllegalAccessException e) {
                    throw new HORMException("illegal argument or Access:", e);
                }
            }
        }

        sql.append(setSQL, 0, setSQL.length() - 2);
        if (fluentSql.getConditionSQL().length() > 0) {
            sql.append(" WHERE ").append(fluentSql.getConditionSQL().substring(FS.AND_STR.length()));
        }

        List<Object> paramsListFinal = new ArrayList<>();
        List<Object> paramsListPre = fluentSql.getParamValues();
        if (paramsListPre == null) {
            paramsListPre = new ArrayList<>();
        }
        paramsListFinal.addAll(paramsUpdate);
        paramsListFinal.addAll(paramsListPre);

        int len = paramsListFinal.size();

        Object[] params = null;

        if (len > 0) {
            params = new Object[len];
            paramsListFinal.toArray(params);
        }

        return Pair.of(sql.toString(), params);
    }

    public static Pair buildInsertSQL(FS<?> fluentSql) {

        StringBuilder columnNames = new StringBuilder();
        StringBuilder placeholder = new StringBuilder();
        List<Object> insertParams = new ArrayList<Object>();

        StringBuilder sql = new StringBuilder();
        if (StrKit.isNotBlank(fluentSql.getHint())) {
            sql.append(fluentSql.getHint()).append(" ");
        }

        sql.append("INSERT INTO ").append(fluentSql.getTableName());
        boolean hasField = false;

        if (null != fluentSql.getUpdateColumns() && !fluentSql.getUpdateColumns().isEmpty()) {

            Map<String, Object> uc = fluentSql.getUpdateColumns();
            Set<Map.Entry<String, Object>> entrySets = uc.entrySet();
            for (Map.Entry<String, Object> entry : entrySets) {
                columnNames.append(",").append(entry.getKey());
                placeholder.append(",?");
                insertParams.add(entry.getValue());
                hasField = true;
            }

            if (BaseModel.class.isAssignableFrom(fluentSql.getModelClass())) {
                // 设置更新时间
                String gmtModify = fluentSql.mapColumn("gmtModify");
                if (!fluentSql.getUpdateColumns().containsKey(gmtModify)) {
                    columnNames.append(",").append(gmtModify);
                    placeholder.append(",?");
                    insertParams.add(new Date());
                }
                // 设置插入时间
                String gmtCreate = fluentSql.mapColumn("gmtCreate");
                if (!fluentSql.getUpdateColumns().containsKey(gmtCreate)) {
                    columnNames.append(",").append(gmtCreate);
                    placeholder.append(",?");
                    insertParams.add(new Date());
                }
            }

        } else {
            if (null != fluentSql.getModel()) {

                ModelFabric mf = ModelCacheUtil.getModelFabric(fluentSql.getModelClass());
                Map<String, TypeHandler<?>> typeHandlerMap = mf.getTypeHandlerMap();
                // 获取所有字段
                List<Field> fields = mf.getFields();
                Map<String, String> attrs = mf.getAttrMapping();
                if (BaseModel.class.isAssignableFrom(fluentSql.getModelClass())) {
                    Date c = new Date();
                    ((BaseModel) fluentSql.getModel()).setGmtModify(c);
                    ((BaseModel) fluentSql.getModel()).setGmtCreate(c);
                }

                try {
                    for (Field field : fields) {

                        field.setAccessible(true);
                        String fieldName = field.getName();
                        Object v = field.get(fluentSql.getModel());
                        String f = attrs.get(fieldName);
                        if (f != null && v != null) {// 找到字段

                            columnNames.append(",").append(f);
                            placeholder.append(",?");
                            insertParams.add(convertValue(typeHandlerMap.get(fieldName), v));
                            hasField = true;
                        }
                    }
                } catch (IllegalArgumentException | IllegalAccessException e) {
                    throw new HORMException("illegal argument or Access:", e);
                }
            }
        }

        if (!hasField) {
            throw new HORMException("no insert field detected");
        }

        sql.append("(").append(columnNames.substring(1)).append(")").append(" VALUES (")
                .append(placeholder.substring(1)).append(")");

        List<Object> paramsListFinal = new ArrayList<>(insertParams);
        int len = paramsListFinal.size();
        Object[] params = null;

        if (len > 0) {
            params = new Object[len];
            paramsListFinal.toArray(params);
        }

        return Pair.of(sql.toString(), params);
    }

    public static Pair buildDeleteSQL(FS<?> fluentSql) {

        List<Object> paramsDelete = new ArrayList<Object>();

        StringBuilder sql = new StringBuilder();
        if (StrKit.isNotBlank(fluentSql.getHint())) {
            sql.append(fluentSql.getHint()).append(" ");
        }

        sql.append("DELETE FROM ").append(fluentSql.getTableName());

        if (fluentSql.getConditionSQL().length() > 0) {
            sql.append(" WHERE ").append(fluentSql.getConditionSQL().substring(FS.AND_STR.length()));
            List<Object> paramsListPre = fluentSql.getParamValues();
            if (paramsListPre == null) {
                paramsListPre = new ArrayList<>();
            }
            paramsDelete.addAll(paramsListPre);
        } else {
            if (null != fluentSql.getModel()) {

                StringBuilder columnNames = new StringBuilder();
                ModelFabric mf = ModelCacheUtil.getModelFabric(fluentSql.getModelClass());
                Map<String, TypeHandler<?>> typeHandlerMap = mf.getTypeHandlerMap();
                // 获取所有字段
                List<Field> fields = mf.getFields();
                Map<String, String> attrs = mf.getAttrMapping();
                String andStr = " AND ";
                try {
                    for (Field field : fields) {

                        field.setAccessible(true);
                        String fieldName = field.getName();
                        Object v = field.get(fluentSql.getModel());
                        String f = attrs.get(fieldName);
                        if (f != null && v != null) {
                            columnNames.append(f).append(" = ?").append(andStr);
                            paramsDelete.add(convertValue(typeHandlerMap.get(fieldName), v));
                        }
                    }
                } catch (IllegalArgumentException | IllegalAccessException e) {
                    throw new HORMException("illegal argument or Access:", e);
                }

                if (columnNames.length() > 0) {
                    sql.append(" WHERE ").append(columnNames, 0, columnNames.length() - andStr.length());
                }
            }
        }

        List<Object> paramsListFinal = new ArrayList<>(paramsDelete);
        int len = paramsListFinal.size();

        Object[] params = null;

        if (len > 0) {
            params = new Object[len];
            paramsListFinal.toArray(params);
        }

        return Pair.of(sql.toString(), params);
    }

    public static Pair buildCountSQL(FS<?> fluentSql) {

        if (!fluentSql.isCount()) {
            throw new HORMException("not count sql");
        }

        StringBuilder sql = new StringBuilder();
        if (StrKit.isNotBlank(fluentSql.getHint())) {
            sql.append(fluentSql.getHint()).append(" ");
        }

        sql.append("SELECT COUNT(*) ");

        sql.append("FROM ").append(fluentSql.getTableName());
        if (fluentSql.getConditionSQL().length() > 0) {
            sql.append(" WHERE ").append(fluentSql.getConditionSQL().substring(FS.AND_STR.length()));
        }

        int len = fluentSql.getParamValues() == null || fluentSql.getParamValues().isEmpty() ? 0
                : fluentSql.getParamValues().size();
        Object[] params = null;

        if (len > 0) {
            params = new Object[len];
            fluentSql.getParamValues().toArray(params);
        }

        return Pair.apply(sql.toString(), params);
    }

    public static String buildExcludedColumns(Class<?> clz, List<String> excludedColumns, boolean useSymbol, String preEscape,
                                              String postEscape) {

        ModelFabric mf = ModelCacheUtil.getModelFabric(clz);
        Map<String, String> attrs = mf.getRawAttrMapping();
        Set<String> columns = new HashSet<String>();
        Set<Map.Entry<String, String>> kvsets = attrs.entrySet();
        for (Map.Entry<String, String> entry : kvsets) {
            if (excludedColumns.contains(entry.getKey()) || excludedColumns.contains(entry.getValue())
                    || excludedColumns.contains(entry.getValue().toLowerCase())) {
                continue;
            }

            if (useSymbol) {
                columns.add(preEscape + entry.getValue() + postEscape);
            } else {
                columns.add(entry.getValue().toUpperCase());
            }
        }

        StringBuilder sb = new StringBuilder();
        columns.forEach(k -> sb.append(k).append(","));
        if (sb.length() > 0) {
            sb.deleteCharAt(sb.length() - 1);
        }
        return sb.toString();
    }

    static Object convertValue(TypeHandler<?> typeHandler, Object v) {
        if (typeHandler == null) {
            return v;
        }

        TypedParameter tp = new TypedParameter();
        tp.setParameter(v);
        tp.setTypeHandler(typeHandler);

        return tp;
    }
}
